import pymssql

class SQLServerDAO:
    def __init__(self, server, user, password, database):
        self.server = '192.168.27.1'
        self.user = 'sa'
        self.password = '123456'
        self.database = 'education'
        self.connection = None
        self.cursor = None

    def connect(self):
        try:
            self.connection = pymssql.connect(server=self.server, user=self.user, password=self.password, database=self.database)
            self.cursor = self.connection.cursor()
        except pymssql.Error as e:
            print("Error connecting to SQL Server:", e)

    def insert(self, table, data):
        columns = ', '.join(data.keys())
        values = ', '.join([f"'{value}'" if isinstance(value, str) else str(value) for value in data.values()])
        query = f"INSERT INTO {table} ({columns}) VALUES ({values})"
        try:
            self.cursor.execute(query)
            self.connection.commit()
        except pymssql.Error as e:
            print("Error inserting data:", e)

    def update(self, table, data, condition):
        set_clause = ', '.join([f"{key} = '{value}'" if isinstance(value, str) else f"{key} = {value}" for key, value in data.items()])
        query = f"INSERT INTO {table} ({set_clause}) WHERE {condition}"
        try:
            self.cursor.execute(query)
            self.connection.commit()
        except pymssql.Error as e:
            print("Error updating data:", e)

    def delete(self, table, condition):
        query = f"DELETE FROM {table} WHERE {condition}"
        try:
            self.cursor.execute(query)
            self.connection.commit()
        except pymssql.Error as e:
            print("Error deleting data:", e)

    def find_by_id(self, table, id):
        query = f"SELECT * FROM {table} WHERE id = {id}"
        try:
            self.cursor.execute(query)
            result = self.cursor.fetchone()
            if result:
                return {col: value for col, value in zip(self.cursor.column_names, result)}
            return None
        except pymssql.Error as e:
            print("Error finding data by id:", e)

    def find_all(self, table):
        query = f"SELECT * FROM {table}"
        try:
            self.cursor.execute(query)
            results = self.cursor.fetchall()
            data = []
            for result in results:
                data.append({col: value for col, value in zip(self.cursor.column_names, result)})
            return data
        except pymssql.Error as e:
            print("Error finding all data:", e)

    def close(self):
        if self.cursor:
            try:
                self.cursor.close()
            except pymssql.Error as e:
                print("Error closing cursor:", e)
        if self.connection:
            try:
                self.connection.close()
            except pymssql.Error as e:
                print("Error closing connection:", e)

